package org.csu.mypetstore.persistence.impl;

import com.sun.jndi.dns.DnsUrl;
import org.csu.mypetstore.domain.Cart;
import org.csu.mypetstore.domain.CartItem;
import org.csu.mypetstore.domain.Item;
import org.csu.mypetstore.domain.Product;
import org.csu.mypetstore.persistence.CartDAO;
import org.csu.mypetstore.persistence.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class CartDAOImpl implements CartDAO {

    private static final String INSERT_CART = "INSERT INTO cart (username,itemid,productid,Description,quantity,inStock,total) VALUES (?,?,?,?,?,?,?)";
    private static final String DELETECART = "DELETE * FROM cart WHERE username = ?";
    private static final String GET_CART = "SELECT itemid,productid,Description,quantity,inStock,total FROM cart WHERE username = ?";
    private static final String GET_ITEM = "SELECT * FROM item WHERE itemid = ?";
    private static final String GET_PRODUCT = "SELECT * FROM product WHERE productid = ?";
    private static final String DELETE_CARTITEM_BY_ID = "DELETE * FROM cart WHERE itemid = ?";
    private static final String UPDATE = "UPDATE cart SET quantity = ? WHERE itemid = ? AND username = ?";
    @Override
    public void insertCart(Item item,String username) {

        try {
            if (item == null){
                System.out.println("CartDAOImpl 21 cartitem = null");
            }
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_CART);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,item.getItemId());
            preparedStatement.setString(3,item.getProductId());
            preparedStatement.setString(4,item.getProduct().getDescription());
            preparedStatement.setInt(5,item.getQuantity());
            //preparedStatement.setString(6,String.valueOf(item);
            //preparedStatement.setBigDecimal(7,item);
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void deleteCart(String username) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(DELETECART);
            preparedStatement.setString(1,username);
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }

    }

    @Override
    public Cart getCart(String username) {
        Cart cart = null;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_CART);
            PreparedStatement preparedStatementItem = connection.prepareStatement(GET_ITEM);
            PreparedStatement preparedStatementProduct = connection.prepareStatement(GET_PRODUCT);
            preparedStatement.setString(1,username);
            cart = new Cart();
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                CartItem cartItem = new CartItem();
                Item item = new Item();
                Product product = new Product();
                String itemid = resultSet.getString(1);
                String productid = resultSet.getString(2);
                preparedStatementItem.setString(1,itemid);
                preparedStatementProduct.setString(1,productid);
                ResultSet resultSetItem = preparedStatementItem.executeQuery();
                ResultSet resultSetProduct = preparedStatementProduct.executeQuery();

                if (resultSetProduct.next()){
                    product.setProductId(productid);
                    product.setCategoryId(resultSetProduct.getString(2));
                    product.setName(resultSetProduct.getString(3));
                    product.setDescription(resultSetProduct.getString(4));

                    if (resultSetItem.next()){
                        item.setItemId(itemid);
                        item.setProductId(productid);
                        item.setListPrice(resultSetItem.getBigDecimal(3));
                        item.setUnitCost(resultSetItem.getBigDecimal(4));
                        item.setSupplierId(resultSetItem.getInt(5));
                        item.setStatus(resultSetItem.getString(6));
                        item.setAttribute1(resultSetItem.getString(7));
                        item.setAttribute2(resultSetItem.getString(8));
                        item.setAttribute3(resultSetItem.getString(9));
                        item.setAttribute4(resultSetItem.getString(10));
                        item.setAttribute5(resultSetItem.getString(11));
                        item.setProduct(product);
                    }
                }
                cartItem.setItem(item);
                cartItem.setQuantity(resultSet.getInt(6));
                cartItem.setInStock(Boolean.parseBoolean(resultSet.getString(7)));
                cartItem.setTotal(resultSet.getBigDecimal(8));

                cart.addItem(item,cartItem.isInStock(),cartItem.getQuantity());
                DBUtil.closeResultSet(resultSetItem);
                DBUtil.closeResultSet(resultSetProduct);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closePreparedStatement(preparedStatementItem);
            DBUtil.closePreparedStatement(preparedStatementProduct);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        System.out.println(cart.getCartItemList()+" CartDAOImpl 121");
        return cart;
    }

    @Override
    public void deleteCartItemById(String itemId) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(DELETE_CARTITEM_BY_ID);
            preparedStatement.setString(1,itemId);
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void update(String itemid,String username,int quantitr){
        System.out.println(itemid);
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE);
            preparedStatement.setInt(1,quantitr);
            preparedStatement.setString(2,itemid);
            preparedStatement.setString(3,username);
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

}
